package com.mk.cloud.common.util;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;

import jxl.read.biff.BiffException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * excel读取工具类
 *
 */
public class XlsUtils{

    /**
     * 合并多个文件到一个workbook中
     * @param workbook
     * @param files
     * @param deleteOnfinish
     * @throws Exception
     * @throws BiffException
     */
    public static void mergeExcel(HSSFWorkbook workbook,List<String> files,boolean deleteOnfinish) throws Exception{
        for(String fileName : files){
            mergeExcel(workbook, fileName, deleteOnfinish);
        }
    }

    /**
     * 合并文件到一个workbook中
     * @param workbook
     * @param deleteOnfinish
     * @throws Exception
     * @throws BiffException
     */
    public static void mergeExcel(HSSFWorkbook workbook,String fileName,boolean deleteOnfinish) throws Exception{
        File file = new File(fileName);
        if(file.exists()){
            InputStream fis = new FileInputStream(file);
            HSSFWorkbook wb = new HSSFWorkbook(fis);
            int numberOfSheets = wb.getNumberOfSheets();
            for(int j = 0 ;j < numberOfSheets ; j++){
                HSSFSheet fromSheet = wb.getSheetAt(j);
                String sheetName = fromSheet.getSheetName();

                if(workbook.getSheetIndex(sheetName) > -1){
                    sheetName += j;
                }
                HSSFSheet toSheet = workbook.createSheet(sheetName);
                copySheet(toSheet, fromSheet, workbook, wb, true);
            }
            fis.close();
            fis = null;
            if(deleteOnfinish){
                file.delete();
                file = null;
            }
        }
    }

    /**
     * 合并多个文件到一个workbook中
     * @param workbook
     * @param files
     * @throws Exception
     * @throws BiffException
     */
    public static void mergeExcel(HSSFWorkbook workbook,List<InputStream> files) throws Exception{
        for(InputStream fis : files){
            mergeExcel(workbook, fis);
        }
    }

    /**
     * 将流对应的文件导入到workbook生成一个新的sheet
     * @param workbook
     * @param fis
     * @throws Exception
     */
    public static void mergeExcel(HSSFWorkbook workbook,InputStream fis) throws Exception{
        HSSFWorkbook wb = new HSSFWorkbook(fis);
        int numberOfSheets = wb.getNumberOfSheets();
        for(int j = 0 ;j < numberOfSheets ; j++){
            HSSFSheet fromSheet = wb.getSheetAt(j);
            String sheetName = fromSheet.getSheetName();

            if(workbook.getSheetIndex(sheetName) > -1){
                sheetName += j;
            }
            HSSFSheet toSheet = workbook.createSheet(sheetName);

            copySheet(toSheet, fromSheet, workbook, wb, true);
        }
        fis.close();
        fis = null;
    }


    /**
     * 获取到excel的数据
     * @param fileStream
     * @param sheetIndex  读取第几个sheet,第一个=0,越界返回null
     * @param startRow  第几行开始,第一行=0
     * @param cellCount 要读取几列?如果单元格内容为空或者超出下标,则对应的数据为null;同时此参数决定了返回结果中的object数组的长度
     * @param noEmptyCell 下标数组,如果下标对应的单元格内容为空,则忽略该行,第一列=0;可传null
     * @return 返回List，元素是object数组
     * @throws IOException
     */
    public static List<Object[]> getData(InputStream fileStream,int sheetIndex,int startRow,int cellCount,Integer[] noEmptyCell) throws IOException, InvalidFormatException, EncryptedDocumentException {
        Workbook workbook=WorkbookFactory.create(fileStream);
        return getData(workbook,sheetIndex,startRow,cellCount,noEmptyCell);
    }

    /**
     * 获取到excel的数据
     * @param wookbook
     * @param sheetIndex  读取第几个sheet,第一个=0,越界返回null
     * @param startRow  第几行开始,第一行=0
     * @param cellCount 要读取几列?如果单元格内容为空或者超出下标,则对应的数据为null;同时此参数决定了返回结果中的object数组的长度
     * @param noEmptyCell 下标数组,如果下标对应的单元格内容为空,则忽略该行,第一列=0 可传null
     * @return 返回List，元素是object数组
     * @throws IOException
     */
    public static List<Object[]> getData(Workbook wookbook, int sheetIndex, int startRow, int cellCount, Integer[] noEmptyCell) throws IOException{
        if(wookbook == null) return null;
        List<Object[]> list = new ArrayList<Object[]>();
        Sheet sheet = wookbook.getSheetAt(sheetIndex);
        if(sheet == null) return null;
        int rows = sheet.getPhysicalNumberOfRows();
        L : for (int i = startRow; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Object[] str = new Object[cellCount];
                List arrList=Arrays.asList(noEmptyCell);
                for (int j = 0; j < cellCount; j++) {
                    Cell cell = row.getCell(j);
                    if(cell == null){
                        if(noEmptyCell != null && arrList.contains(j)){
                            continue L;
                        }
                        str[j] = null;
                        continue;
                    }
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            str[j] =cell.getNumericCellValue();
                            break;
                        case Cell.CELL_TYPE_STRING:
                            str[j] =cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            str[j] = cell.getNumericCellValue();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            if(noEmptyCell != null && arrList.contains(j)){
                                continue L;
                            }
                            str[j] = null;
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            str[j] = cell.getBooleanCellValue();
                            break;
                        default:
                            if(noEmptyCell != null && arrList.contains(j)){
                                continue L;
                            }
                            str[j] = null;
                            break;
                    }
                }
                list.add(str);
            }
        }
        return list;
    }

    /**
     * 获取到excel的数据,将所有的sheet都读取到一个数组中
     * @param fileStream
     * @param startRow  第几行开始,第一行=0
     * @param cellCount 要读取几列?如果单元格内容为空或者超出下标,则对应的数据为null;同时此参数决定了返回结果中的object数组的长度
     * @param noEmptyCell 下标数组,如果下标对应的单元格内容为空,则忽略该行,第一列=0 可传null
     * @return 返回List，元素是object数组
     * @throws IOException
     */
    public static List<Object[]> getData(InputStream fileStream,int startRow,int cellCount,Integer[] noEmptyCell) throws IOException{
        return getData(new HSSFWorkbook(fileStream),startRow,cellCount,noEmptyCell);
    }
    /**
     * 获取到excel的数据,将所有的sheet都读取到一个数组中
     * @param wookbook
     * @param startRow  第几行开始,第一行=0
     * @param cellCount 要读取几列?如果单元格内容为空或者超出下标,则对应的数据为null;同时此参数决定了返回结果中的object数组的长度
     * @param noEmptyCell 下标数组,如果下标对应的单元格内容为空,则忽略该行,第一列=0 可传null
     * @return 返回List，元素是object数组
     * @throws IOException
     */
    public static List<Object[]> getData(HSSFWorkbook wookbook,int startRow,int cellCount,Integer[] noEmptyCell) throws IOException{
        if(wookbook == null) return null;
        List<Object[]> list = new ArrayList<Object[]>();
        int sheet = wookbook.getNumberOfSheets();
        for(int i = 0;i < sheet;i++){
            List<Object[]> temp = getData(wookbook, i,startRow, cellCount, noEmptyCell);
            if(temp != null) list.addAll(temp);
        }
        return list;
    }
    /**
     * 获取某一行的数据，需要指定返回的数组的长度，即读取这一行的几列
     * @param fileStream
     * @param sheetIndex  读取第几个sheet,第一个=0
     * @param rowIndex 第几行.第一行=0
     * @param cellCount 要读取几列?如果单元格内容为空或者超出下标,则对应的数据为null;同时此参数决定了返回结果中的object数组的长度
     * @return 返回数组,数组长度由 cellCount决定
     * @throws IOException
     */
    public static Object[] getRowData(InputStream fileStream,int sheetIndex,int rowIndex,int cellCount) throws IOException{
        return getRowData(new HSSFWorkbook(fileStream),sheetIndex,rowIndex,cellCount);
    }
    /**
     * 获取某一行的数据，需要指定返回的数组的长度，即读取这一行的几列
     * @param wookbook
     * @param sheetIndex  读取第几个sheet,第一个=0
     * @param rowIndex 第几行.第一行=0
     * @param cellCount 要读取几列?如果单元格内容为空或者超出下标,则对应的数据为null;同时此参数决定了返回结果中的object数组的长度
     * @return 返回数组,数组长度由 cellCount决定
     * @throws IOException
     */
    public static Object[] getRowData(HSSFWorkbook wookbook,int sheetIndex,int rowIndex,int cellCount) throws IOException{
        if(wookbook == null) return null;
        HSSFSheet sheet = wookbook.getSheetAt(sheetIndex);
        if(sheet == null) return null;
        HSSFRow row = sheet.getRow(rowIndex);
        if(row == null) return null;
        return getRowData(row,cellCount);
    }
    /**
     * 获取某一行的数据，返回数据只读取非空的单元格
     * @param wookbook
     * @param sheetIndex  读取第几个sheet,第一个=0
     * @param rowIndex 第几行.第一行=0
     * @return 返回数组,数组长度由 cellCount决定
     * @throws IOException
     */
    public static Object[] getRowData(HSSFWorkbook wookbook,int sheetIndex,int rowIndex) throws IOException{
        if(wookbook == null) return null;
        HSSFSheet sheet = wookbook.getSheetAt(sheetIndex);
        if(sheet == null) return null;
        HSSFRow row = sheet.getRow(rowIndex);
        if(row == null) return null;
        return getRowData(row);
    }
    /**
     * 获取某一行的数据，返回数据只读取非空的单元格
     * @param fileStream
     * @param sheetIndex  读取第几个sheet,第一个=0
     * @param rowIndex 第几行.第一行=0
     * @return 返回数组,数组长度由 cellCount决定
     * @throws IOException
     */
    public static Object[] getRowData(InputStream fileStream,int sheetIndex,int rowIndex) throws IOException{
        return  getRowData(new HSSFWorkbook(fileStream),sheetIndex,rowIndex);
    }
    /**
     * 获取某一行的数据，需要指定返回的数组的长度，即读取这一行的几列
     * @param row
     * @param cellCount 要读取几列?如果单元格内容为空或者超出下标,则对应的数据为null;同时此参数决定了返回结果中的object数组的长度
     * @return 返回数组,数组长度由 cellCount决定
     * @throws IOException
     */
    public static Object[] getRowData(HSSFRow row,int cellCount) throws IOException{
        if (row != null) {
            if(cellCount == 0) cellCount = row.getPhysicalNumberOfCells();
            Object[] str = new Object[cellCount];
            for (int j = 0; j < cellCount; j++) {
                HSSFCell cell = row.getCell(j);
                if(cell == null){
                    str[j] = null;
                    continue;
                }
                switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        str[j] =cell.getNumericCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        str[j] =cell.getStringCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        str[j] = cell.getNumericCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_BLANK:
                        str[j] = null;
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        str[j] = cell.getBooleanCellValue();
                        break;
                    default:
                        str[j] = null;
                        break;
                }
            }
            return str;
        }
        return null;
    }
    /**
     * 获取某一行的数据，返回数据只读取非空的单元格
     * @param row
     * @return
     * @throws IOException
     */
    public static Object[] getRowData(HSSFRow row) throws IOException{
        return getRowData(row,0);
    }

    /**
     * 功能：拷贝sheet
     * 实际调用     copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true)
     * @param targetSheet
     * @param sourceSheet
     * @param targetWork
     * @param sourceWork
     */
    public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet,
                                 HSSFWorkbook targetWork, HSSFWorkbook sourceWork) throws Exception{
        if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){
            throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");
        }
        copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true);
    }

    /**
     * 功能：拷贝sheet
     * @param targetSheet
     * @param sourceSheet
     * @param targetWork
     * @param sourceWork
     * @param copyStyle                 boolean 是否拷贝样式
     */
    public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet,
                                 HSSFWorkbook targetWork, HSSFWorkbook sourceWork, boolean copyStyle)throws Exception {

        if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){
            throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");
        }

        //复制源表中的行
        int maxColumnNum = 0;

        Map styleMap = (copyStyle) ? new HashMap() : null;

        HSSFPatriarch patriarch = targetSheet.createDrawingPatriarch(); //用于复制注释
        for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) {
            HSSFRow sourceRow = sourceSheet.getRow(i);
            HSSFRow targetRow = targetSheet.createRow(i);

            if (sourceRow != null) {
                copyRow(targetRow, sourceRow,
                        targetWork, sourceWork,patriarch, styleMap);
                if (sourceRow.getLastCellNum() > maxColumnNum) {
                    maxColumnNum = sourceRow.getLastCellNum();
                }
            }
        }

        //复制源表中的合并单元格
        mergerRegion(targetSheet, sourceSheet);

        //设置目标sheet的列宽
        for (int i = 0; i <= maxColumnNum; i++) {
            targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));
        }
    }

    /**
     * 功能：拷贝row
     * @param targetRow
     * @param sourceRow
     * @param styleMap
     * @param targetWork
     * @param sourceWork
     * @param targetPatriarch
     */
    public static void copyRow(HSSFRow targetRow, HSSFRow sourceRow,
                               HSSFWorkbook targetWork, HSSFWorkbook sourceWork,HSSFPatriarch targetPatriarch, Map styleMap) throws Exception {
        if(targetRow == null || sourceRow == null || targetWork == null || sourceWork == null || targetPatriarch == null){
            throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时，targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空，故抛出该异常！");
        }

        //设置行高
        targetRow.setHeight(sourceRow.getHeight());

        for (int i = sourceRow.getFirstCellNum(); i <= sourceRow.getLastCellNum(); i++) {
            HSSFCell sourceCell = sourceRow.getCell(i);
            HSSFCell targetCell = targetRow.getCell(i);

            if (sourceCell != null) {
                if (targetCell == null) {
                    targetCell = targetRow.createCell(i);
                }

                //拷贝单元格，包括内容和样式
                copyCell(targetCell, sourceCell, targetWork, sourceWork, styleMap);

                //拷贝单元格注释
                copyComment(targetCell,sourceCell,targetPatriarch);
            }
        }
    }

    /**
     * 功能：拷贝cell，依据styleMap是否为空判断是否拷贝单元格样式
     * @param targetCell            不能为空
     * @param sourceCell            不能为空
     * @param targetWork            不能为空
     * @param sourceWork            不能为空
     * @param styleMap              可以为空
     */
    public static void copyCell(HSSFCell targetCell, HSSFCell sourceCell, HSSFWorkbook targetWork, HSSFWorkbook sourceWork,Map styleMap) {
        if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){
            throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！");
        }

        //处理单元格样式
        if(styleMap != null){
            if (targetWork == sourceWork) {
                targetCell.setCellStyle(sourceCell.getCellStyle());
            } else {
                String stHashCode = "" + sourceCell.getCellStyle().hashCode();
                HSSFCellStyle targetCellStyle = (HSSFCellStyle) styleMap
                        .get(stHashCode);
                if (targetCellStyle == null) {
                    targetCellStyle = targetWork.createCellStyle();
                    targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
                    styleMap.put(stHashCode, targetCellStyle);
                }

                targetCell.setCellStyle(targetCellStyle);
            }
        }

        //处理单元格内容
        switch (sourceCell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                targetCell.setCellValue(sourceCell.getRichStringCellValue());
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                targetCell.setCellValue(sourceCell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                targetCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                targetCell.setCellFormula(sourceCell.getCellFormula());
                break;
            default:
                break;
        }
    }

    /**
     * 功能：拷贝comment
     * @param targetCell
     * @param sourceCell
     * @param targetPatriarch
     */
    public static void copyComment(HSSFCell targetCell,HSSFCell sourceCell,HSSFPatriarch targetPatriarch)throws Exception{
        if(targetCell == null || sourceCell == null || targetPatriarch == null){
            throw new IllegalArgumentException("调用PoiUtil.copyCommentr()方法时，targetCell、sourceCell、targetPatriarch都不能为空，故抛出该异常！");
        }

        //处理单元格注释
        HSSFComment comment = sourceCell.getCellComment();
        if(comment != null){
            HSSFComment newComment = targetPatriarch.createComment(new HSSFClientAnchor());
            newComment.setAuthor(comment.getAuthor());
            newComment.setColumn(comment.getColumn());
            newComment.setFillColor(comment.getFillColor());
            newComment.setHorizontalAlignment(comment.getHorizontalAlignment());
            newComment.setLineStyle(comment.getLineStyle());
            newComment.setLineStyleColor(comment.getLineStyleColor());
            newComment.setLineWidth(comment.getLineWidth());
            newComment.setMarginBottom(comment.getMarginBottom());
            newComment.setMarginLeft(comment.getMarginLeft());
            newComment.setMarginTop(comment.getMarginTop());
            newComment.setMarginRight(comment.getMarginRight());
            newComment.setNoFill(comment.isNoFill());
            newComment.setRow(comment.getRow());
            newComment.setShapeType(comment.getShapeType());
            newComment.setString(comment.getString());
            newComment.setVerticalAlignment(comment.getVerticalAlignment());
            newComment.setVisible(comment.isVisible());
            targetCell.setCellComment(newComment);
        }
    }

    /**
     * 功能：复制原有sheet的合并单元格到新创建的sheet
     *
     * @param targetSheet
     * @param sourceSheet
     */
    public static void mergerRegion(HSSFSheet targetSheet, HSSFSheet sourceSheet)throws Exception {
        if(targetSheet == null || sourceSheet == null){
            throw new IllegalArgumentException("调用PoiUtil.mergerRegion()方法时，targetSheet或者sourceSheet不能为空，故抛出该异常！");
        }

        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            CellRangeAddress oldRange = sourceSheet.getMergedRegion(i);
            CellRangeAddress newRange = new CellRangeAddress(
                    oldRange.getFirstRow(), oldRange.getLastRow(),
                    oldRange.getFirstColumn(), oldRange.getLastColumn());
            targetSheet.addMergedRegion(newRange);
        }
    }

    /**
     * 功能：重新定义HSSFColor.YELLOW的色值
     *
     * @param workbook
     * @return
     */
    public static HSSFColor setMForeColor(HSSFWorkbook workbook) {
        HSSFPalette palette = workbook.getCustomPalette();
        HSSFColor hssfColor = null;
        byte[] rgb = { (byte) 221, (byte) 241, (byte) 255 };
        try {
            hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]);
            if (hssfColor == null) {
                palette.setColorAtIndex(HSSFColor.YELLOW.index, rgb[0], rgb[1],
                        rgb[2]);
                hssfColor = palette.getColor(HSSFColor.YELLOW.index);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hssfColor;
    }
    /**
     * 功能：重新定义HSSFColor.PINK的色值
     *
     * @param workbook
     * @return
     */
    public static HSSFColor setMBorderColor(HSSFWorkbook workbook) {
        HSSFPalette palette = workbook.getCustomPalette();
        HSSFColor hssfColor = null;
        byte[] rgb = { (byte) 0, (byte) 128, (byte) 192 };
        try {
            hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]);
            if (hssfColor == null) {
                palette.setColorAtIndex(HSSFColor.PINK.index, rgb[0], rgb[1],
                        rgb[2]);
                hssfColor = palette.getColor(HSSFColor.PINK.index);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hssfColor;
    }

    public static void main(String[] args) throws Exception {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        File file = new File("d:/1.xls");
        file.createNewFile();
        OutputStream out = new FileOutputStream(file);

        File file2 = new File("d:/a.xls");
        InputStream inputStream = new FileInputStream(file2);
        HSSFWorkbook src = new HSSFWorkbook(inputStream);

        copySheet(hssfWorkbook.createSheet(), src.getSheetAt(0), hssfWorkbook, src, true);
        hssfWorkbook.write(out);
        out.flush();
        out.close();
        inputStream.close();

    }

}
